﻿using Dapper;
using OMG.Fill.Mulberry.Model;
using OMG.Fill.Mulberry.Model.Ent;
using ProjectShare;
using System;
using System.Collections.Generic;
using System.Linq;
namespace OMG.Fill.Mulberry.Service
{
    /// <summary>
    /// 桑蚕种场生产经营情况调查表通用操作类
    /// </summary>
    public class MulSilkwormFieldServices:MulSilkwormFields
    {
        public Pager<List<MulSilkwormFieldEntity>> GetPageList(MulSilkwormFieldQuery query)
        {
            Pager<List<MulSilkwormFieldEntity>> obj = new Pager<List<MulSilkwormFieldEntity>>();
            var paramlist = new DynamicParameters();
            string strWhereSql = "";//from 表在下面，需完善其他查询条件在这里接的写，格式为："and xxx=@xxx空格,如下注释部分代码所示："
            //if (!string.IsNullOrEmpty(query.GroupCode))
            //{
            //  strWhereSql += "and GroupCode = @GroupCode ";
            //  paramlist.Add("GroupCode", query.GroupCode);
            //}
            string strFromSql = "from dbo.MulSilkwormField where 1=1 " + strWhereSql;
            string strTotal = "select count(1)" + strFromSql + strWhereSql;
            string strOrderSql = "";//自定义完成order by语句
            string strSql = @"select * from (select *,ROW_NUMBER() over (order by ID ) as RowNumber " + strFromSql + ") as t ";
            strSql += "where t.RowNumber between  @minRow and @maxRow" + strOrderSql;
            paramlist.Add("minRow", query.MinRow);
            paramlist.Add("maxRow", query.MaxRow);
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    obj.rows = dbCon.Query<MulSilkwormFieldEntity>(strSql,paramlist).ToList();
                    obj.total = dbCon.Query<int>(strTotal,paramlist).FirstOrDefault();
                    obj.status = DefaultKey.SuccessResult;
                    obj.message = "获取成功！";
                    dbCon.Dispose();//数据库连接释放
                    return obj;
                }
            }
            catch (Exception ex)
            {
                obj.status = DefaultKey.ErrorResult;
                obj.message = ex.ToString().Trim();
                LogHelper.ErrorLog("获取桑蚕种场生产经营情况调查表分页列表失败", ex);
                return obj;
            }
        }
        /// <summary>
        /// 获取指定对象
        /// </summary>
        public ResResult<List<MulSilkwormFieldEntity>> GetList(string ReportId)
        {
            ResResult<List<MulSilkwormFieldEntity>> obj = new ResResult<List<MulSilkwormFieldEntity>>();
            MulSilkwormFieldEntity model = new MulSilkwormFieldEntity();
            if (CheckRepCount(ReportId))
            {
                model.ReportId = ReportId;
                model.Remark = "";
                if (AddInfo(model).status > 0)
                {
                    obj.model = new List<MulSilkwormFieldEntity>();
                    obj.model.Add(model);
                    obj.status = DefaultKey.SuccessResult;
                    obj.message = "获取成功！";
                }
            }
            else
            {
                string strSql = @"select * from dbo.MulSilkwormField where ReportId = @ReportId";
                try
                {
                    using (var dbCon = ConnectFactory.GetOpenConnection())
                    {
                        obj.model = dbCon.Query<MulSilkwormFieldEntity>(strSql, new { ReportId = ReportId }).ToList();
                        obj.status = DefaultKey.SuccessResult;
                        obj.message = "获取成功！";
                        dbCon.Dispose();//数据库连接释放
                    }
                }
                catch (Exception ex)
                {
                    obj.status = DefaultKey.ErrorResult;
                    obj.message = ex.ToString().Trim();
                    LogHelper.ErrorLog("获取桑蚕种场生产经营情况调查表信息失败", ex);

                }
            }
            return obj;
        }

        /// <summary>
        /// 数据汇总
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public ResResult<List<MulSilkwormFieldInfoModel>> GetMulSFIList(MulNowModel model)
        {
            ResResult<List<MulSilkwormFieldInfoModel>> result = new ResResult<List<MulSilkwormFieldInfoModel>>();
            DictServices dictServices = new DictServices();
            if (!string.IsNullOrEmpty(model.FillInYear))
            {
                model.FillInYear = dictServices.GetPZKey(model.FillInYear, "ReportYear");
            }
            //统计代码
            string StrSql = "select DictText year,UnitName Name,isnull(PersonalTotal,0)PersonalTotal,";
            StrSql += "isnull(KJRYNum,0)KJRYNum,isnull(GJZCNum,0)GJZCNum,isnull(SCYFMJ,0)SCYFMJ,";
            StrSql += "isnull(ZYSYMJ,0)ZYSYMJ,isnull(SCZCL_YZ,0)SCZCL_YZ,isnull(SCZCL_ZJ,0)SCZCL_ZJ,";
            StrSql += "isnull(CZXS_YZ,0)CZXS_YZ,isnull(CZXS_ZJ,0)CZXS_ZJ,isnull(FixedAssets,0)FixedAssets,";
            StrSql += "isnull(MulSilkwormProduct,0)MulSilkwormProduct,isnull(Cost,0)Cost,isnull(Profit,0)Profit,msf.Remark ";
            StrSql += "from T_Report Report inner join MulSilkwormField msf on Report.Id = msf.ReportId  ";
            StrSql += "left join T_Dict Dict on Report.FillInYear = Dict.DictValue and GroupCode = 'ReportYear'  ";
            StrSql += "left join T_Area Area on Report.AreaCode = Area.Code  ";
            StrSql += "left join T_User u on u.AreaCode = Area.Code and Account like '%czc%'  ";
            StrSql += "where Report.FillInYear = @FillInYear and Report.Status=@ReportStatus  ";
            if (model.AreaCode != "530000000000")//判断省级用户
            {
                StrSql += "and Area.ParentCode=@AreaCode ";
            }
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    var paramlist = new DynamicParameters();
                    paramlist.Add("AreaCode", model.AreaCode);
                    paramlist.Add("FillInYear", model.FillInYear);
                    paramlist.Add("ReportStatus", model.Status);
                    List<MulSilkwormFieldInfoModel> list = new List<MulSilkwormFieldInfoModel>();
                    List<MulSilkwormFieldInfoModel> list1 = new List<MulSilkwormFieldInfoModel>();
                    list = dbCon.Query<MulSilkwormFieldInfoModel>(StrSql, paramlist).ToList();
                    foreach (var msf in list)
                    {
                        list1.Add(msf);
                    }
                    List<MulSilkwormFieldInfoModel> list2 = new List<MulSilkwormFieldInfoModel>();
                    MulSilkwormFieldInfoModel m = new MulSilkwormFieldInfoModel();
                    //m.AreaName = "合计";
                    m.year = "合计";
                    m.Name = list1.Where(s => s.Name != null).Count().ToString();
                    m.PersonalTotal = list1.Sum(s => s.PersonalTotal);
                    m.KJRYNum = list1.Sum(s => s.KJRYNum);
                    m.GJZCNum = list1.Sum(s => s.GJZCNum);
                    m.SCYFMJ = list1.Sum(s => s.SCYFMJ);
                    m.ZYSYMJ = list1.Sum(s => s.ZYSYMJ);
                    m.SCZCL_YZ = list1.Sum(s => s.SCZCL_YZ);
                    m.SCZCL_ZJ = list1.Sum(s => s.SCZCL_ZJ);
                    m.CZXS_YZ = list1.Sum(s => s.CZXS_YZ);
                    m.CZXS_ZJ = list1.Sum(s => s.CZXS_ZJ);
                    m.FixedAssets = list1.Sum(s => s.FixedAssets);
                    m.MulSilkwormProduct = list1.Sum(s => s.MulSilkwormProduct);
                    m.Cost = list1.Sum(s => s.Cost);
                    m.Profit = list1.Sum(s => s.Profit);
                    list2.Add(m);
                    foreach (var item in list1)
                    {
                        list2.Add(item);
                    }

                    result.model = list2;
                    if (list != null)
                    {
                        result.status = DefaultKey.SuccessResult;
                        result.message = "获取蚕种场生产经营情况调查统计数据成功！";
                    }
                    else
                    {
                        result.status = DefaultKey.InfoResult;
                        result.message = "蚕种场生产经营情况调查统计数据为空！";
                    }
                    dbCon.Dispose();//数据库连接释放
                }
            }
            catch (Exception ex)
            {
                result.status = DefaultKey.ErrorResult;
                result.message = ex.ToString().Trim();
                LogHelper.ErrorLog("获取蚕种场生产经营情况调查统计数据失败", ex);
            }
            return result;
        }
    }
}